package com.liliya.dao.impl;

import com.liliya.dao.BaseDao;
import com.liliya.dao.CateDao;
import com.liliya.pojo.Cate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hhy
 * @Date 2021/1/9 17:31
 */
public class CateDaoImpl extends BaseDao implements CateDao {
    @Override
    public void insertCate(Cate cate) {
        String sql = "insert into T_CATE (CATENAME, CATEDES, CATEIMGLINK, CATESTYLE,CATENUM)\n" +
                "values (?,?,?,?,CATEID.nextval)";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, cate.getCatename());
            pstm.setString(2, cate.getCatedes());
            pstm.setString(3, cate.getCateimglink());
            pstm.setString(4, cate.getCatestyle());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public void deleteCate(Integer cateNum) {
        String sql = "delete T_CATE where catenum = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, cateNum);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public void updateCate(Cate cate) {
        String sql = "update T_CATE set  CATENAME=?,CATEDES = ?,CATEIMGLINK = ?,CATESTYLE = ? where CATENUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, cate.getCatename());
            pstm.setString(2, cate.getCatedes());
            pstm.setString(3, cate.getCateimglink());
            pstm.setString(4, cate.getCatestyle());
            pstm.setInt(5, cate.getCatenum());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public Cate findCateByNum(Integer cateNum) {
        Cate cate = null;
        String sql = "select CATENAME, CATEDES, CATEIMGLINK, CATESTYLE,CATENUM from T_CATE where CATENUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, cateNum);
            rs = pstm.executeQuery();
            while (rs.next()) {
                cate = new Cate();
                cate.setCatename(rs.getString("CATENAME"));
                cate.setCatedes(rs.getString("CATEDES"));
                cate.setCateimglink(rs.getString("CATEIMGLINK"));
                cate.setCatestyle(rs.getString("CATESTYLE"));
                cate.setCatenum(rs.getInt("CATENUM"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return cate;
    }

    @Override
    public Cate findCateByRow(Integer rowNum) {
        Cate cate = null;
        String sql = "select catename, catedes, cateimglink, catestyle,catenum \n" +
                "from (select catename, catedes, cateimglink, catestyle,catenum,ROWNUM r from T_CATE) where r = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, rowNum);
            rs = pstm.executeQuery();
            while (rs.next()) {
                cate = new Cate();
                cate.setCatename(rs.getString("catename"));
                cate.setCatedes(rs.getString("catedes"));
                cate.setCateimglink(rs.getString("cateimglink"));
                cate.setCatestyle(rs.getString("catestyle"));
                cate.setCatenum(rs.getInt("catenum"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return cate;
    }

    @Override
    public int totalCate() {
        String sql = "select count(1) c from T_CATE";
        Integer count = 0;
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return count;
    }

    @Override
    public List<Cate> findCate(Integer start, Integer end) {
        List<Cate> cates = new ArrayList<>();
        String sql = "select catename, catedes, cateimglink, catestyle,catenum\n" +
                "from (select catename, catedes, cateimglink, catestyle,catenum,ROWNUM r from T_CATE) where r between ? and ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,start);
            pstm.setInt(2,end);
            rs = pstm.executeQuery();
            while (rs.next()){
                Cate cate = new Cate();
                cate.setCatename(rs.getString("catename"));
                cate.setCatedes(rs.getString("catedes"));
                cate.setCateimglink(rs.getString("cateimglink"));
                cate.setCatestyle(rs.getString("catestyle"));
                cate.setCatenum(rs.getInt("catenum"));
                cates.add(cate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return cates;
    }
}
